package com.csvw.skr.console.service;

import com.csvw.skr.basic.entity.Organization;
import com.google.common.collect.Lists;

import com.webonn.frame.core.Pagination;
import com.webonn.frame.db.MyDao;
import com.webonn.frame.util.MyStringUtil;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.ws.rs.core.MultivaluedMap;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2016/11/22.
 */
@Service
public class OrganizationService {
    @Autowired
    private MyDao myDao;

    public Pagination queryOrganization(int start, int limit, MultivaluedMap<String, String> filter) {
        Pagination p = new Pagination();
        p.setStart(start);
        p.setLimit(limit);
        String sql = "select * from csvw_skr_basic_organization where 1=1 ";
        List<Object> paramsList = Lists.newArrayList();

        String search = filter.getFirst("search");
        if (!MyStringUtil.isNullOrEmpty(search)) {
            sql += " and CONCAT_WS(',', name, description,pk_code) like ? ";
            paramsList.add("%" + search.trim() + "%");
        }
        sql += " order by created desc ";
        return myDao.listPagination(sql, p, Organization.class, paramsList.toArray());
    }

    public Pagination queryOrganizationSelection(int start, int limit, String search) {
        Pagination p = new Pagination();
        p.setStart(start);
        p.setLimit(limit);
        String sql = "select name as text, pk_code as id from csvw_skr_basic_organization where active=true and pk_code!= 'Kcc' ";
        List<Object> paramsList = Lists.newArrayList();

        if (!MyStringUtil.isNullOrEmpty(search)) {
            sql += " and CONCAT_WS(',', name, description, pk_code) like ? ";
            paramsList.add("%" + search.trim() + "%");
        }
        return myDao.queryResultToMapPagination(sql, p, paramsList.toArray());
    }

    public Organization addOrganization(Organization organization) {
        Date addTime = new Date();
        organization.setCreated(addTime);
        organization.setUpdated(addTime);
        organization.setActive(true);
        return myDao.persist(organization);
    }

    public Organization updateOrganization(Organization organization) {
        Organization entity_old = getOrganization(organization.getCode());
        organization.setCreated(entity_old.getCreated());
        organization.setActive(entity_old.isActive());
        organization.setUpdated(new Date());
        return myDao.persist(organization);
    }

    public Organization updateOrganizationAndRelation(Organization organization) {
        Organization entity_old = myDao.get(organization.getCode(), Organization.class);

        Session session = myDao.openSession();
        Transaction tx = session.beginTransaction();

        try {
            organization.setCreated(entity_old.getCreated());
            organization.setActive(entity_old.isActive());
            organization.setUpdated(new Date());

            session.saveOrUpdate(organization);
            if (!entity_old.getName().equals(organization.getName())) {
                this.updateOrganizationRelation(organization, session);
            }

            tx.commit();
            return organization;

        } catch (Exception e) {
            e.printStackTrace();
            tx.rollback();
            return null;
        } finally {
            if (session != null) {
                session.close();
            }
        }
    }

    /**
     * 更新组织名称时，更新报表中的组织名称
     * 若更新的组织名称是大区的名称，不更新
     */
    private void updateOrganizationRelation(Organization organization, Session session) throws Exception {
        String[] tables = {"csvw_skr_report_otd", "csvw_skr_report_dealer_stock", "csvw_skr_report_dealer_stock_account", "csvw_skr_report_forwardsales"};

        // String[] tables2 =  {"csvw_skr_report_day_target","csvw_skr_report_day_target_rbo","csvw_skr_report_month_predict",
        //     "csvw_skr_report_dealer_deposit","csvw_skr_report_dealer_leads","csvw_skr_report_invoice","csvw_skr_report_month_target","csvw_skr_report_month_target_rbo",
        //     "csvw_skr_report_rebate_retail","csvw_skr_report_retail","csvw_skr_report_wholesale"};

        String sql = "UPDATE @table SET @rboName = ? " +
                "WHERE @rbo = ? AND created = (SELECT tb.created FROM (SELECT created FROM @table ORDER BY created DESC LIMIT 1) AS tb)";

        // String sql2= "UPDATE @table SET @rboName = ? " +
        //         "WHERE @rbo = ? AND YEAR(created) = YEAR(CURDATE()) AND ( MONTH(created) = MONTH(CURDATE()) OR MONTH(created) = MONTH(CURDATE())-1 )";

        List<Object> params = Lists.newArrayList();
        params.add(organization.getName());
        params.add(organization.getCode());

        switch (organization.getLevel()) {
            /*case 2:
                sql = sql.replace("@rboName", "rboName").replace("@rbo", "rbo");
                break;*/
            case 3:
                sql = sql.replace("@rboName", "commercialName").replace("@rbo", "commercial");
                break;
            case 4:
                sql = sql.replace("@rboName", "areaName").replace("@rbo", "area");
                break;
        }
        for (String table : tables) {
            SQLQuery q = myDao.bindSQLQueryParams(session.createSQLQuery(sql.replace("@table", table)), params.toArray());
            boolean b = q.executeUpdate() >= 0;
            if (!b)
                throw new Exception();
        }
    }


    public Organization getOrganization(String organizationCode) {
        return myDao.get(organizationCode, Organization.class);
    }

    public Organization deleteOrganization(String organizationCode) {
        Organization del = myDao.get(organizationCode, Organization.class);
        return myDao.delete(del);
    }

    public String getOrganizationInfo(String orgId) {
        Organization organization = myDao.get(orgId, Organization.class);
        return this.getOrganizationInfo(organization);
    }

    public String getOrganizationInfo(Organization organization) {

        if (null == organization) return "";

        String sql = "SELECT org.`name`,org.`level` FROM csvw_skr_basic_organization org WHERE " +
                "? LIKE concat('%', org.pk_code, '%') AND pk_code!='1' AND org.`level`>1 " +
                "AND org.`level` < ? ORDER BY org.`level` ";
        List<Object> param = Lists.newArrayList();
        param.add(organization.getCode());
        param.add(organization.getLevel());
        List<Map<String, Object>> list = myDao.queryResultToMap(sql, param.toArray());
        String orgLocation = "";
        for (Map<String, Object> map : list) {
            orgLocation += map.get("name") + "-";
        }
        orgLocation += organization.getName();
        return orgLocation;
    }
}
